<?php
  $dbHost = 'localhost';
  $dbSchema = 'macdadi';
  $dbUser = 'macdadi-read';
  $dbPwd = '';

  $projectId = 1; // GSB

  $viewPrefix = 'v_';

  $allowedViews = array(
    'goals', 
    'preferences',
    'options', 
    'impacts',
    'stakeholders',
    'stakeholdergroups' );

  // SELECT all rows from specified view:
  // projectId is hard-coded above, view and output encoding are URL parameters
  selectView($dbHost, $dbSchema, $dbUser, $dbPwd, $projectId, $viewPrefix, $allowedViews);

  function displayError($msg)
  {
    startHTML();
    print "<p style='{color:red}'>$msg</p>\n";
    endHTML();
  }
    
  function selectView($host, $schema, $user, $pwd, $projectId, $viewPrefix, $allowedViews)
  {
    $link = mysql_connect($host, $user, $pwd);
    if(!is_resource($link)) {
      displayError('Could not connect: ' . mysql_error());
      return;
    }

    if(!mysql_select_db($schema)) { 
      displayError('Could not select the database: ' . mysql_error());
      return;
    }

    $viewName = $_GET['view'];
    $encoding = $_GET['enc'];

    // Reverse magic_quotes_gpc
    if(get_magic_quotes_gpc()) {
      $viewName = stripslashes($viewName);
      $encoding = stripslashes($encoding);
    } 

    $viewName = mysql_real_escape_string($viewName, $link);

    if(! in_array($viewName, $allowedViews) ) {
      displayError("The specified view $viewName is not available");
      return;
    }

    $viewName = $viewPrefix . $viewName;

    
    $query = "SELECT * FROM $viewName WHERE project_id = $projectId";

    $result = mysql_query($query); 
    if(! is_resource($result)) { 
      displayError('Query failed: ' . mysql_error()); 
      return;
    }

    if ( $encoding == 'json' ) {
      printJSON($result);
    } else if ( $encoding == 'html' ) { 
      printHTML($result);
    } else {
      displayError("Please choose an encoding as indicated.");
    }

    // Free resultset
    mysql_free_result($result);
    
    // Closing connection
    mysql_close($link);
  }

  function printJSON($result)
  {
    $arr = array();
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      $arr[] = $row;
    }
    print json_encode($arr);
  }


  function printHTML($result)
  {
    startHTML();

    print "<table>\n";

    // Print the column names as header, from first row
    $line = mysql_fetch_array($result, MYSQL_ASSOC);
    print "\t<tr>\n";
    while ( list( $col_name ) = each( $line ) ) {
      print "\t\t<th>$col_name</th>\n";
    }
    print "\t</tr>\n";
    mysql_data_seek($result, 0);

    // After resetting result set, print each row's values
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
      print "\t<tr>\n";
      foreach ($line as $col_value) {
        print "\t\t<td>$col_value</td>\n";
      }
      print "\t</tr>\n";
    }

    print "</table>\n";

    endHTML();
  }

  function startHTML()
  {
?>   

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8559-1">
<title>MACDADI Data</title>
</head>
<body>

<?php
  }

  function endHTML()
  {
?>

<hr />

<table cellpadding="3">
 <tr><td colspan="3">URL Parameters</td></tr>
 <tr>
  <td>Name:</td>
  <td>Legal values:</td>
  <td>Description:</td>
 </td>
 <tr>
  <td><b>view</b></td>
  <td>goals, preferences, options, impacts, stakeholders, stakeholdergroups</td>
  <td>Name of view in MySQL database, e.g. view=goals</td>
 </tr>
 <tr>
  <td><b>enc</b></td>
  <td>json, html</td>
  <td>Encoding format for result, e.g. enc=json</td>
 </tr>
 <tr>
  <td colspan="3">Example: <a href="?view=goals&enc=json">goals view  as JSON</a></td>
 </tr>
</table>

<p>Script and page created by Ben Suter, copyright 2008 Ben Suter - <a href="http://www.fuzzysymmetry.com">fuzzysymmetry</a></p>

</body>
</html>

<?php
  }
?>    

